package manager.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import manager.entitycreator.EntityCreator;

/**
 * A manager class to manage database connection and execute queries.
 * 
 * @author huangjm.2012, mohameds.2012, sheryl.tay.2012
 * 
 */

public class DatabaseMgr {
	private Connection conn;
	private PreparedStatement stmt;
	private ResultSet rs;

	/**
	 * Generic method to retrieve a single entity object from database.
	 * 
	 * @param entityCreator
	 * @param sql
	 * @param param
	 * @return an entity object of type T
	 */
	public <T> T retrieveEntityForQuery(EntityCreator<T> entityCreator,
			String sql, Object... param) {

		T entity = null;

		try {
			ResultSet rs = openAndRead(sql, param);
			while (rs.next()) {
				entity = entityCreator.newEntityFromResultSet(rs);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close();
		}

		return entity;
	}

	/**
	 * Generic method to retrieve a multiple entity objects from database.
	 * 
	 * @param entityCreator
	 * @param sql
	 * @param param
	 * @return an array list of entity objects of type T
	 */
	public <T> ArrayList<T> retrieveMultipleEntitiesForQuery(
			EntityCreator<T> entityCreator, String sql, Object... param) {

		ArrayList<T> entities = new ArrayList<T>();

		try {
			ResultSet rs = openAndRead(sql, param);
			while (rs.next()) {
				T entity = entityCreator.newEntityFromResultSet(rs);
				entities.add(entity);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close();
		}

		return entities;

	}

	public int retrieveSingleIntegerForQuery(String sql, Object... param) {

		int value = 0;

		try {
			ResultSet rs = openAndRead(sql, param);
			while (rs.next()) {
				value = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close();
		}

		return value;
	}
	
	public double retrieveSingleDoubleForQuery(String sql, Object... param) {

		double value = 0;

		try {
			ResultSet rs = openAndRead(sql, param);
			while (rs.next()) {
				value = rs.getDouble(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close();
		}

		return value;
	}

	/**
	 * Open database connection and execute a read query. Returns result set for
	 * query.
	 * 
	 * @param sql
	 *            the SQL statement to execute
	 * @param param
	 *            sequence of parameters to be used in the SQL statement
	 * @return resultSet a result set object containing results for the executed
	 *         query
	 * @throws SQLException
	 *             if a database connection cannot be opened or an SQL error
	 *             occurs while executing the query
	 */
	public ResultSet openAndRead(String sql, Object... param)
			throws SQLException {
		open();
		setStatement(sql, param);
		rs = stmt.executeQuery();
		return rs;
	}

	/**
	 * Open database connection and execute an update, delete or insert query.
	 * Returns number of rows affected.
	 * 
	 * @param sql
	 *            the SQL statement to execute
	 * @param param
	 *            sequence of parameters to be used in the SQL statement
	 * @return numRowsAffected number of rows affected by the executed query
	 */
	public int executeUpdate(String sql, Object... param) {
		try {
			open();
			setStatement(sql, param);
			return stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close();
		}

		return 0;
	}

	/**
	 * Open connection to database. Remember to close connection after use.
	 * 
	 * @throws SQLException
	 *             if a database connection cannot be opened
	 */
	public void open() throws SQLException {
		conn = DatabaseConnectionMgr.open();
	}

	/**
	 * Prepare statement for the opened connection.
	 * 
	 * @param sql
	 *            the SQL statement to execute
	 * @param param
	 *            a sequence of parameters (optional) to be used in the SQL
	 *            statement
	 * @throws SQLException
	 *             if the database connection is not open
	 */
	public void setStatement(String sql, Object... param) throws SQLException {
		if (conn == null) {
			throw new SQLException("Connection is not open.");
		}

		// prepare SQL statement for connection
		stmt = conn.prepareStatement(sql);
		// setting different parameters in sequence
		for (int i = 0; i < param.length; i++) {
			stmt.setObject(i + 1, param[i]);
		}
	}

	/**
	 * Sets parameters and adds it to the batch.
	 * 
	 * @param param
	 *            a sequence of parameters (optional) to be used in the SQL
	 *            statement
	 * @throws SQLException
	 *             if the database connection is not open; if statement is not
	 *             set
	 */
	public void addBatch(Object... param) throws SQLException {
		if (conn == null) {
			throw new SQLException("Connection is not open.");
		}
		if (stmt == null) {
			throw new SQLException("Statement is not set.");
		}
		// setting different parameters in sequence
		for (int i = 0; i < param.length; i++) {
			stmt.setObject(i + 1, param[i]);
		}
		stmt.addBatch();
	}

	/**
	 * Executes an update, delete or insert query.
	 * 
	 * @return the number of rows affected
	 * @throws SQLException
	 *             if the database connection is not open; if statement is not
	 *             set
	 */
	public int[] executeBatch() throws SQLException {
		if (conn == null) {
			throw new SQLException("Connection is not open.");
		}
		if (stmt == null) {
			throw new SQLException("Statement is not set.");
		}
		return stmt.executeBatch();
	}

	/**
	 * Close the database connection. Should always be called after opening a
	 * database connection.
	 */
	public void close() {
		DatabaseConnectionMgr.close(conn, stmt, rs);
	}

}
